from openpyxl import load_workbook
from openpyxl_image_loader import SheetImageLoader
import os
from datetime import datetime
from openpyxl.utils import range_boundaries
from util.common import folder_path

#来源：https://blog.csdn.net/weixin_44380466/article/details/131864657
#来源：https://blog.csdn.net/weixin_46783197/article/details/139320666
#来源：https://blog.csdn.net/weixin_52710755/article/details/136543199
folder_path = folder_path('excel')

# 定义一个每个表格有多少数据的
excel_total_data = [175,139,58,9,24,31,39,66,19,17]

# 选择工作表
workbook = load_workbook('../upload/excel/sample/product.xlsx')
sheet_names = workbook.sheetnames
for key,item in enumerate(sheet_names):
    if key !=4 :
        continue
    sheet = workbook[item]
    image_loader = SheetImageLoader(sheet)
    path = folder_path + "/" + item
    if not os.path.exists(path):
        os.makedirs(path)

    # 获取合并单元格数据的最大值和最小值
    merged_cell_range = list(sheet.merged_cells.ranges)
    merged_cell_C2 = [cell for cell in merged_cell_range if cell.coord[0]=='C']
    merged_data = []
    for merged_cell in merged_cell_C2:
        min_col, min_row, max_col, max_row = merged_cell.bounds
        obj = {'min':min_row,'max':max_row} 
        merged_data.append(obj)
    print(item,"merge_data",merged_data)

    # excel表中id,barcode字典
    excel_barcodeList = ['0']
    for index, row in enumerate(sheet.rows, start=1):
        data = {"id": index,"barcode":row[3].value}
        if index < excel_total_data[key]:
            excel_barcodeList.append(row[3].value)
    print(excel_barcodeList,len(excel_barcodeList))
    
    for index, row in enumerate(sheet.rows, start=1):
        if row[3].value == None:
            continue
        name = row[3].value
        print(index,name)
        if image_loader.image_in(f'C{index}'):
            image = image_loader.get(f'C{index}')
            if name != None :
                if '\n' in name:
                    name = name.split('\n')[0]
                image.save(path+'/'+ name + ".png")
                for item in merged_data:
                    if(index >= item['min'] and index <= item['max']):
                        for i in range(item['min'], item['max']+1):
                            name = excel_barcodeList[i]
                            if name != None :
                                if '\n' in name:
                                    name = name.split('\n')[0]
                                image.save(path+'/'+ name + ".png")

    image_loader._images.clear()
    workbook.close()
